package com.hakgc.rent.dao.impl;


import com.hakgc.rent.dao.BaseDao;
import com.hakgc.rent.dao.StreetDao;
import com.hakgc.rent.entity.District;
import com.hakgc.rent.entity.Street;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author Xing.Jiejian
 * @date 2020/10/19
 */
public class StreetDaoImpl extends BaseDao implements StreetDao {
    @Override
    public boolean save(Street street) {
        String sql = "INSERT hos_street(streetName,did) VALUES(?,?)";
        return super.executeUpdate(sql,street.getName(),street.getDistrictId())>0;
    }

    @Override
    public boolean update(Street street) {
        String sql = "UPDATE hos_street SET streetName=?, did=? WHERE sid=?";
        Object[] params = {street.getName(),street.getDistrictId(),street.getId()};
        return executeUpdate(sql,params)>0;
    }

    @Override
    public boolean deleteById(int id) {
        String sql = "DELETE FROM hos_street WHERE sid=?";
        return executeUpdate(sql,id)>0;
    }

    @Override
    public Street findById(int id) {
        String sql = "SELECT sid, streetName, did FROM hos_street WHERE sid=?";
        List<Street> streets = getListFromRS(executeQuery(sql,id));
        if (streets!=null&&streets.size()>0){
            return streets.get(0);
        }
        return null;
    }

    @Override
    public List<Street> findAll() {
        String sql = "SELECT sid, streetName, did FROM hos_street";
        return getListFromRS(executeQuery(sql));
    }

    @Override
    public List<Street> findAllWithDistrict() {
        String sql = "SELECT s.sid, s.streetName, s.did, d.dName FROM hos_street s " +
                "JOIN hos_district d ON s.did=d.did";
        return getListWithDistrictFromRS(executeQuery(sql));
    }

    @Override
    public List<Street> findByDistrictId(int districtId) {
        String sql = "SELECT sid, streetName, did FROM hos_street WHERE did=?";
        return getListFromRS(executeQuery(sql,districtId));
    }

    @Override
    public List<Street> findByLimit(int offset, int limit) {
        String sql = "SELECT sid, streetName, did FROM hos_street LIMIT ?,?";
        return getListFromRS(executeQuery(sql,offset,limit));
    }

    @Override
    public List<Street> findByLimit(String likeName, int did, int offset, int limit) {
        StringBuilder sql = new StringBuilder("SELECT sid, streetName, did FROM hos_street WHERE 1=1 ");
        List<Object> paramList = new ArrayList<>();
        // 当要查询的街道名不为空时，组装查询条件
        if (likeName != null && !"".equals(likeName)) {
            sql.append("AND streetName LIKE ? ");
            paramList.add("%"+likeName+"%");
        }
        // 当区县编号为正确范围时，组装区县查询条件
        if (did>0){
            sql.append("AND did=? ");
            paramList.add(did);
        }
        sql.append("LIMIT ?,?");
        paramList.add(offset);
        paramList.add(limit);
        return getListFromRS(executeQuery(sql.toString(),paramList.toArray()));
    }

    @Override
    public List<Street> findByPage(int pageNo, int pageSize) {
        int offset = (pageNo-1)*pageSize;
        return findByLimit(offset,pageSize);
    }

    @Override
    public int getAllCount() {
        String sql = "SELECT COUNT(sid) FROM hos_street";
        return (int)(long)uniqueResult(sql);
    }

    @Override
    public int getCount(String likeName, int did) {
        // 字符串拼接使用StringBuilder提高效率
        StringBuilder sql = new StringBuilder("SELECT COUNT(sid) FROM hos_street WHERE 1=1 ");
        List<Object> paramList = new ArrayList<>();
        // 当要查询的街道名不为空时，组装查询条件
        if (likeName != null && !"".equals(likeName)) {
            sql.append("AND streetName LIKE ? ");
            paramList.add("%"+likeName+"%");
        }
        // 当区县编号为正确范围时，组装区县查询条件
        if (did>0){
            sql.append("AND did=? ");
            paramList.add(did);
        }
        // 将结合类型的参数转换成数组传递给查询方法
        return (int)(long)uniqueResult(sql.toString(),paramList.toArray());
    }

    /**
     * 从结果集中获取街道集合
     * @param rs 包含街道的结果集
     * @return 街道集合
     */
    private List<Street> getListFromRS(ResultSet rs){
        List<Street> streets = null;
        try {
            if (rs != null) {
                streets = new ArrayList<>();
                while (rs.next()){
                    Street street = new Street();
                    street.setId(rs.getInt("sid"));
                    street.setName(rs.getString("streetName"));
                    street.setDistrictId(rs.getInt("did"));
                    streets.add(street);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 注意：解析完结果集之后一定要按照顺序关闭所有数据库对象
            closeAll();
        }
        return streets;
    }


    /**
     * 从结果集中获取关联区县的街道集合
     * @param rs 包含区县及街道的结果集
     * @return 关联区县的街道集合
     */
    private List<Street> getListWithDistrictFromRS(ResultSet rs){
        List<Street> streets = null;
        try {
            if (rs != null) {
                streets = new ArrayList<>();
                while (rs.next()){
                    // 街道
                    Street street = new Street();
                    street.setId(rs.getInt("sid"));
                    street.setName(rs.getString("streetName"));
                    street.setDistrictId(rs.getInt("did"));
                    // 关联的区县对象
                    District district = new District();
                    district.setId(street.getDistrictId());
                    district.setName(rs.getString("dName"));
                    // 组装街道和区县
                    street.setDistrict(district);
                    streets.add(street);
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            // 注意：解析完结果集之后一定要按照顺序关闭所有数据库对象
            closeAll();
        }
        return streets;
    }
}
